if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCertificationQuestionnaireAnswer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCertificationQuestionnaireAnswer]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************
* Name:			GetCertificationQuestionnaireAnswer
* Purpose:		Retrieves Training Questionnaire Answer 
*
* PARAMETERS
* Name    		Description					
* -------------  	-------------------------------------------
* @admPersonId	
* @questionnaireAnswerName
******************************************************************/

Create Procedure [dbo].[GetCertificationQuestionnaireAnswer] (    
	@admPersonId	INTEGER,  
	@questionnaireAnswerName varchar(256)  
)    
AS    
BEGIN    
    
	SET NOCOUNT ON    
    
	SELECT QA.Id,  
     		QA.QuestionnaireVersionId,   
     		QA.Name,  
    		QA.Required,  
     		QA.Editable,  
     		Q.Category as 'QuestionnaireCategory',  
     		QA.LastModifiedDate,  
     		QA.ExternalId,  
     		QA.IsClone,  
     		QA.ActionTypeId,  
     		QA.Highlight,
	 	QA.LastModifiedByAdmPersonId ,
	 	ADP.LastName+', '+ ADP.FirstName  as LastModifiedBy
	FROM AdmPerson_QuestionnaireAnswer_Map map
		INNER JOIN QuestionnaireAnswer QA ON map.QuestionnaireAnswerId = QA.Id
		inner join QuestionnaireVersion QV on QV.[Id] = QA.QuestionnaireVersionId
		INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId
		LEFT JOIN AdmPerson ADP
	  		on ADP.Id = QA.LastModifiedByAdmPersonId 
	WHERE QA.Name = @questionnaireAnswerName
		AND Map.AdmPersonId = @admPersonId

      
END    

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF  